﻿----------------------------------------- Ahmed Khled 2025-12-09 -----------------


INSERT INTO [SystemMenu](SystemMenuCode,Name,Arabic_Name,ParentID,ControllerName,ActionName,ImageURL,HTTPMethod,Sequence,IsVisible,ShowInPrivilegePage) VALUES (1210,N'Employees',N'الموظفين',27,N'/Personal/Employees',N'Index',N'fas fa-users',N'get',1,1,1)
GO
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1210,22)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4300,N'الموظفين',N'Employees',1210,1)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4301,N'عرض الموظفين',N'View Employees',1210,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4302,N'إضافة موظف',N'Add Employee',1210,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4303,N'تعديل موظف',N'Edit Employee',1210,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4304,N'حذف موظف',N'Delete Employee',1210,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4305,N'حفظ موظف',N'Save Employee',1210,2)
GO
----------------------------------------- End 2025-12-09 -----------------
 
 
----------------------------------------- Mostafa 2025-12-09 -----------------
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION

DECLARE @s NVARCHAR(MAX)='';

-- Drop FK coming from other tables (incoming)
SELECT @s+= 'ALTER TABLE ['+OBJECT_SCHEMA_NAME(fk.parent_object_id)+'].['+OBJECT_NAME(fk.parent_object_id)+'] DROP CONSTRAINT ['+fk.name+']; '
FROM sys.foreign_keys fk
WHERE fk.referenced_object_id = OBJECT_ID('dbo.InsuranceTypes');

-- Drop FK inside this table (outgoing)
SELECT @s+= 'ALTER TABLE ['+OBJECT_SCHEMA_NAME(parent_object_id)+'].['+OBJECT_NAME(parent_object_id)+'] DROP CONSTRAINT ['+name+']; '
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID('dbo.InsuranceTypes');

-- Drop PK, UQ, CHECK, DEFAULT
SELECT @s+= 'ALTER TABLE ['+OBJECT_SCHEMA_NAME(parent_object_id)+'].['+OBJECT_NAME(parent_object_id)+'] DROP CONSTRAINT ['+name+']; '
FROM sys.objects
WHERE parent_object_id=OBJECT_ID('dbo.InsuranceTypes')
AND type IN('PK','UQ','C','D');

EXEC(@s);

IF OBJECT_ID(N'dbo.Tmp_InsuranceTypes', 'U') IS NOT NULL
DROP TABLE dbo.Tmp_InsuranceTypes;

CREATE TABLE dbo.Tmp_InsuranceTypes(
InsuranceCode tinyint NOT NULL IDENTITY (0, 1),
Name nvarchar(150),
Arabic_Name nvarchar(150),
BE float(53) NULL,
BC float(53) NULL,
BMAX float(53) NULL,
VE float(53) NULL,
VC float(53) NULL,
VMAX float(53) NULL,
BEOpen bit NULL,
BCOpen bit NULL,
BMAXOpen bit NULL,
VEOpen bit NULL,
VCOpen bit NULL,
VMAXOpen bit NULL,
PercentOfSC float(53) NULL,
Area nvarchar(100) NULL,
Office nvarchar(100) NULL,
CompanyName nvarchar(100) NULL,
HrManager nvarchar(100) NULL,
LowStatus nvarchar(100) NULL,
BuildingNumber nvarchar(50) NULL,
StreetName nvarchar(100) NULL,
Village nvarchar(100) NULL,
Section nvarchar(100) NULL,
City nvarchar(100) NULL,
Insurnumber nvarchar(100) NULL,
MyDate datetime NULL,
AsBe nvarchar(100) NULL,
Confess nvarchar(100) NULL,
MinimumDaysToCalculateInCaseOfResignation tinyint NULL,
MedicalBasicEmployee float(53) NULL,
MedicalBasicCompany float(53) NULL,
MedicalVariableEmployee float(53) NULL,
MedicalVariableCompany float(53) NULL,
PercentOfPool float(53) NULL,
InsuranceForm bit NULL,
MinimumBasic float(53) NULL,
MinimumTotalInsurance float(53) NULL,
MandatoryNationality tinyint NULL,
SupportEmployee float(53) NULL,
SupportCompany float(53) NULL,
PensionEmployee float(53) NULL,
PensionCompany float(53) NULL,
RiskEmployee float(53) NULL,
RiskCompany float(53) NULL
)  ON [PRIMARY]

ALTER TABLE dbo.Tmp_InsuranceTypes SET (LOCK_ESCALATION = TABLE)

ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_BE DEFAULT ((0)) FOR BE
ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_BC DEFAULT ((0)) FOR BC
ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_BMAX DEFAULT ((0)) FOR BMAX
ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_VE DEFAULT ((0)) FOR VE
ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_VC DEFAULT ((0)) FOR VC
ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_VMAX DEFAULT ((0)) FOR VMAX
ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_BEOpen DEFAULT ((0)) FOR BEOpen
ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_BCOpen DEFAULT ((0)) FOR BCOpen
ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_BMAXOpen DEFAULT ((0)) FOR BMAXOpen
ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_VEOpen DEFAULT ((0)) FOR VEOpen
ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_VCOpen DEFAULT ((0)) FOR VCOpen
ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_VMAXOpen DEFAULT ((0)) FOR VMAXOpen
ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_PercentOfSC_IT DEFAULT ((0)) FOR PercentOfSC
ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_InsuranceForm DEFAULT ((0)) FOR InsuranceForm

SET IDENTITY_INSERT dbo.Tmp_InsuranceTypes ON

IF EXISTS(SELECT * FROM dbo.InsuranceTypes)
EXEC('INSERT INTO dbo.Tmp_InsuranceTypes (InsuranceCode, Name, Arabic_Name, BE, BC, BMAX, VE, VC, VMAX, BEOpen, BCOpen, BMAXOpen, VEOpen, VCOpen, VMAXOpen, PercentOfSC, Area, Office, CompanyName, HrManager, LowStatus, BuildingNumber, StreetName, Village, Section, City, Insurnumber, MyDate, AsBe, Confess, MinimumDaysToCalculateInCaseOfResignation, MedicalBasicEmployee, MedicalBasicCompany, MedicalVariableEmployee, MedicalVariableCompany, PercentOfPool, InsuranceForm, MinimumBasic, MinimumTotalInsurance, MandatoryNationality, SupportEmployee, SupportCompany, PensionEmployee, PensionCompany, RiskEmployee, RiskCompany)
SELECT InsuranceCode, Name, Arabic_Name, BE, BC, BMAX, VE, VC, VMAX, BEOpen, BCOpen, BMAXOpen, VEOpen, VCOpen, VMAXOpen, PercentOfSC, Area, Office, CompanyName, HrManager, LowStatus, BuildingNumber, StreetName, Village, Section, City, Insurnumber, MyDate, AsBe, Confess, MinimumDaysToCalculateInCaseOfResignation, MedicalBasicEmployee, MedicalBasicCompany, MedicalVariableEmployee, MedicalVariableCompany, PercentOfPool, InsuranceForm, MinimumBasic, MinimumTotalInsurance, MandatoryNationality, SupportEmployee, SupportCompany, PensionEmployee, PensionCompany, RiskEmployee, RiskCompany FROM dbo.InsuranceTypes WITH (HOLDLOCK TABLOCKX)')

SET IDENTITY_INSERT dbo.Tmp_InsuranceTypes OFF

DROP TABLE dbo.InsuranceTypes

EXECUTE sp_rename N'dbo.Tmp_InsuranceTypes', N'InsuranceTypes', 'OBJECT'

ALTER TABLE dbo.InsuranceTypes ADD CONSTRAINT
PK_InsuranceTypes PRIMARY KEY CLUSTERED (InsuranceCode) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
COMMIT
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='InsuranceLocal' AND Object_ID=Object_ID('InsuranceTypes'))
BEGIN  ALTER TABLE InsuranceTypes ADD InsuranceLocal TINYINT NULL;
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='SCCalculateWay' AND Object_ID=Object_ID('InsuranceTypes'))
BEGIN ALTER TABLE InsuranceTypes ADD SCCalculateWay TINYINT NULL
CONSTRAINT DF_InsuranceTypes_SCCalculateWay DEFAULT ((0));  END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='MinimumDaysToCalculateInCaseOfNewHire' AND Object_ID=Object_ID('InsuranceTypes'))
BEGIN ALTER TABLE InsuranceTypes ADD MinimumDaysToCalculateInCaseOfNewHire TINYINT NULL
CONSTRAINT DF_InsuranceTypes_MinimumDaysToCalculateInCaseOfNewHire DEFAULT ((0));  END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='MaximumInsuranceBasicSalary' AND Object_ID=Object_ID('InsuranceTypes'))
BEGIN ALTER TABLE InsuranceTypes ADD MaximumInsuranceBasicSalary FLOAT NULL
CONSTRAINT DF_InsuranceTypes_MaximumInsuranceBasicSalary DEFAULT ((0));  END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ExemptionPercentage' AND Object_ID=Object_ID('InsuranceTypes'))
BEGIN  ALTER TABLE InsuranceTypes ADD ExemptionPercentage FLOAT NULL
	CONSTRAINT DF_InsuranceTypes_ExemptionPercentage DEFAULT ((0));
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='MaximumExemption' AND Object_ID=Object_ID('InsuranceTypes'))
BEGIN   ALTER TABLE InsuranceTypes ADD MaximumExemption FLOAT NULL
    CONSTRAINT DF_InsuranceTypes_MaximumExemption DEFAULT ((0));
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='MinimumInsuranceBasicSalary' AND Object_ID=Object_ID('InsuranceTypes'))
BEGIN ALTER TABLE InsuranceTypes ADD MinimumInsuranceBasicSalary FLOAT NULL
CONSTRAINT DF_InsuranceTypes_MinimumInsuranceBasicSalary DEFAULT ((0));  END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='InsuranceBasicSalaryFactor' AND Object_ID=Object_ID('InsuranceTypes'))
BEGIN ALTER TABLE InsuranceTypes ADD InsuranceBasicSalaryFactor FLOAT NULL
CONSTRAINT DF_InsuranceTypes_InsuranceBasicSalaryFactor DEFAULT ((0));  END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ActivityStarted' AND Object_ID=Object_ID('InsuranceTypes'))
BEGIN ALTER TABLE InsuranceTypes ADD ActivityStarted NVARCHAR(50) NULL; END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='TaxRegistrationNumber' AND Object_ID=Object_ID('InsuranceTypes'))
BEGIN ALTER TABLE InsuranceTypes ADD TaxRegistrationNumber NVARCHAR(100) NULL; END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='AutomaticYearlyUpdatePercent' AND Object_ID=Object_ID('InsuranceTypes'))
BEGIN ALTER TABLE InsuranceTypes ADD AutomaticYearlyUpdatePercent BIT NULL; END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CalculationIfAppointmentResignationInSameMonth' AND Object_ID=Object_ID('InsuranceTypes'))
BEGIN ALTER TABLE InsuranceTypes ADD CalculationIfAppointmentResignationInSameMonth BIT NULL; END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='InsuranceStatus' AND Object_ID=Object_ID('InsuranceTypes'))
BEGIN
 ALTER TABLE InsuranceTypes ADD InsuranceStatus NVARCHAR(10) NULL;
END
GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreationDate' AND Object_ID=Object_ID('InsuranceTypes'))
BEGIN
ALTER TABLE InsuranceTypes ADD CreationDate DATETIME NOT NULL CONSTRAINT DF_InsuranceTypes_CreationDate DEFAULT (GETDATE());
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreatedBy' AND Object_ID=Object_ID('InsuranceTypes'))
BEGIN
ALTER TABLE InsuranceTypes ADD CreatedBy INT NOT NULL CONSTRAINT DF_InsuranceTypes_CreatedBy DEFAULT (1);
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModifiedBy' AND Object_ID=Object_ID('InsuranceTypes'))
BEGIN ALTER TABLE InsuranceTypes ADD ModifiedBy INT NULL; END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='Closed' AND Object_ID=Object_ID('InsuranceTypes'))
BEGIN
ALTER TABLE InsuranceTypes ADD Closed BIT NULL CONSTRAINT DF_InsuranceTypes_Closed DEFAULT (0);
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModificationDate' AND Object_ID=Object_ID('InsuranceTypes'))
BEGIN ALTER TABLE InsuranceTypes ADD ModificationDate DATETIME NULL; END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='Serial' AND Object_ID=Object_ID('InsuranceTypes'))
BEGIN ALTER TABLE InsuranceTypes ADD Serial NVARCHAR(250) NULL; END
GO
IF EXISTS(SELECT 1 FROM InsuranceTypes WHERE Serial IS NULL)
BEGIN
    UPDATE InsuranceTypes SET Serial = InsuranceCode WHERE Serial IS NULL;
END
GO

SET QUOTED_IDENTIFIER ON;
SET ARITHABORT ON;
SET NUMERIC_ROUNDABORT OFF;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;

-- Set lock escalation for tables
ALTER TABLE dbo.Nationality SET (LOCK_ESCALATION = TABLE);
ALTER TABLE dbo.InsuranceTypes SET (LOCK_ESCALATION = TABLE);

-- Add foreign key constraint
ALTER TABLE dbo.InsuranceTypes
ADD CONSTRAINT FK_InsuranceTypes_Nationality
FOREIGN KEY (MandatoryNationality)
REFERENCES dbo.Nationality (NationalityCode)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
go

INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1215,N'Basis',N'الاسس',1023,NULL,NULL,'fa-solid fa-database',NULL,1,1,1)
Go
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1215,22)
Go
INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1216,N'Insurance Type',N'أنواع التأمين',1215,N'/Personal/InsuranceType',N'Index','fa-solid fa-hand-holding-medical',N'get',1,1,1)
Go
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1216,22)
Go

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4306,N'أنواع التأمين',N'Insurance Type',1216,1)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4307,N'عرض أنواع التأمين',N'View Insurance Type',1216,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4308,N'إضافة أنواع التأمين',N'Add Insurance Type',1216,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4309,N'تعديل أنواع التأمين',N'Edit Insurance Type',1216,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4310,N'حذف أنواع التأمين',N'Delete Insurance Type',1216,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4311,N'حفظ أنواع التأمين',N'Save Insurance Type',1216,2)
Go
----------------------------------------- End 2025-12-09 -----------------
----------------------------------------- Mostafa 2025-12-10 -----------------


BEGIN TRANSACTION;
SET QUOTED_IDENTIFIER ON;
SET ARITHABORT ON;
SET NUMERIC_ROUNDABORT OFF;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;

DECLARE @s NVARCHAR(MAX)='';

-- Drop FK coming from other tables (incoming)
SELECT @s += 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(fk.parent_object_id) + '].[' + OBJECT_NAME(fk.parent_object_id) + '] DROP CONSTRAINT [' + fk.name + ']; '
FROM sys.foreign_keys fk
WHERE fk.referenced_object_id = OBJECT_ID('dbo.Penalties');

-- Drop FK inside this table (outing)
SELECT @s += 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']; '
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID('dbo.Penalties');

-- Drop PK, UQ, CHECK, DEFAULT
SELECT @s += 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']; '
FROM sys.objects
WHERE parent_object_id = OBJECT_ID('dbo.Penalties') AND type IN('PK','UQ','C','D');

EXEC(@s);



    IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='Colsed' AND Object_ID=Object_ID('Penalties'))
BEGIN
ALTER TABLE Penalties ADD [Colsed] BIT  NULL  DEFAULT ((0));
END
GO


-- Drop temp table if exists
IF OBJECT_ID(N'dbo.Tmp_Penalties', 'U') IS NOT NULL
    DROP TABLE dbo.Tmp_Penalties;
CREATE TABLE dbo.Tmp_Penalties
	(
	EmployeePenaltyCode int NOT NULL IDENTITY (0, 1),
	Serial float(53) NOT NULL,
	EmployeeCode int NULL,
	PenaltyCode smallint NULL,
	[Once] tinyint NULL,
	Date datetime NULL,
	Amount float(53) NULL,
	Period tinyint NULL,
	PYear smallint NULL,
	EventDescription varchar(1000) NULL,
	Colsed bit NULL,
	Abate bit NULL,
	AddBy nvarchar(20) NULL,
	AddDate datetime NULL,
	LastChangeBy nvarchar(20) NULL,
	ChangeDate datetime NULL,
	MainType tinyint NULL,
	OriginalPYear int NULL,
	OriginalPeriod tinyint NULL,
	LinkSerial int NULL,
	Closed bit NULL,
	AmountValue float(53) NULL,
	AccApproved int NULL,
	DateHijri nvarchar(10) NULL,
	ExpirationDate datetime NULL,
	ExpirationDateHijri nvarchar(10) NULL,
	EventPlace nvarchar(255) NULL,
	EventTime nvarchar(5) NULL,
	MDT datetime NULL
	)  ON [PRIMARY]

ALTER TABLE dbo.Tmp_Penalties SET (LOCK_ESCALATION = TABLE)

ALTER TABLE dbo.Tmp_Penalties ADD CONSTRAINT DF_Penalties_Serial DEFAULT ((0)) FOR Serial
											 
ALTER TABLE dbo.Tmp_Penalties ADD CONSTRAINT DF_Penalties_EmployeeCode DEFAULT ((0)) FOR EmployeeCode
											 
ALTER TABLE dbo.Tmp_Penalties ADD CONSTRAINT DF_Penalties_PenaltyCode DEFAULT ((0)) FOR PenaltyCode
											 
ALTER TABLE dbo.Tmp_Penalties ADD CONSTRAINT DF_Penalties_Once DEFAULT ((0)) FOR [Once]
											 
ALTER TABLE dbo.Tmp_Penalties ADD CONSTRAINT DF_Penalties_Amount DEFAULT ((0)) FOR Amount
											 
ALTER TABLE dbo.Tmp_Penalties ADD CONSTRAINT DF_Penalties_Period DEFAULT ((0)) FOR Period
											 
ALTER TABLE dbo.Tmp_Penalties ADD CONSTRAINT DF_Penalties_PYear DEFAULT ((0)) FOR PYear
												 
ALTER TABLE dbo.Tmp_Penalties ADD CONSTRAINT DF_Penalties_Abate DEFAULT ((0)) FOR Abate
											 
ALTER TABLE dbo.Tmp_Penalties ADD CONSTRAINT DF_Penalties_Closed DEFAULT ((0)) FOR Closed

SET IDENTITY_INSERT dbo.Tmp_Penalties ON

IF EXISTS(SELECT * FROM dbo.Penalties)
	 EXEC('INSERT INTO dbo.Tmp_Penalties (EmployeePenaltyCode,Serial, EmployeeCode, PenaltyCode, [Once], Date, Amount, Period, PYear, EventDescription, Colsed, Abate, AddBy, AddDate, LastChangeBy, ChangeDate, MainType, OriginalPYear, OriginalPeriod, LinkSerial, Closed, AmountValue, AccApproved, DateHijri, ExpirationDate, ExpirationDateHijri, EventPlace, EventTime, MDT)
		SELECT TRY_CAST(Serial AS int),Serial, EmployeeCode, PenaltyCode, [Once], Date, Amount, Period, PYear, EventDescription, Colsed, Abate, AddBy, AddDate, LastChangeBy, ChangeDate, MainType, OriginalPYear, OriginalPeriod, LinkSerial, Closed, AmountValue, AccApproved, DateHijri, ExpirationDate, ExpirationDateHijri, EventPlace, EventTime, MDT FROM dbo.Penalties WITH (HOLDLOCK TABLOCKX)')

SET IDENTITY_INSERT dbo.Tmp_Penalties OFF;

DROP TABLE dbo.Penalties

EXECUTE sp_rename N'dbo.Tmp_Penalties', N'Penalties', 'OBJECT' 

ALTER TABLE dbo.Penalties ADD CONSTRAINT
	PK_Penalties PRIMARY KEY CLUSTERED (EmployeePenaltyCode) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

COMMIT
GO

IF EXISTS (SELECT 1 FROM sys.columns    WHERE Name = 'AddBy' AND Object_ID = Object_ID('dbo.Penalties'))
BEGIN
    ALTER TABLE dbo.Penalties DROP COLUMN AddBy;
END

IF EXISTS (SELECT 1 FROM sys.columns   WHERE Name = 'AddDate' AND Object_ID = Object_ID('dbo.Penalties'))
BEGIN
    ALTER TABLE dbo.Penalties DROP COLUMN AddDate;
END

IF EXISTS (SELECT 1 FROM sys.columns     WHERE Name = 'LastChangeBy' AND Object_ID = Object_ID('dbo.Penalties'))
BEGIN
    ALTER TABLE dbo.Penalties DROP COLUMN LastChangeBy;
END

IF EXISTS (SELECT 1 FROM sys.columns   WHERE Name = 'ChangeDate' AND Object_ID = Object_ID('dbo.Penalties'))
BEGIN
    ALTER TABLE dbo.Penalties DROP COLUMN ChangeDate;
END

IF EXISTS (SELECT 1 FROM sys.columns  WHERE Name = 'MDT' AND Object_ID = Object_ID('dbo.Penalties'))
BEGIN
    ALTER TABLE dbo.Penalties DROP COLUMN MDT;
END

go 

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreationDate' AND Object_ID=Object_ID('Penalties'))
BEGIN
ALTER TABLE Penalties ADD CreationDate DATETIME NOT NULL CONSTRAINT DF_Penalties_CreationDate DEFAULT (GETDATE());
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreatedBy' AND Object_ID=Object_ID('Penalties'))
BEGIN
ALTER TABLE Penalties ADD CreatedBy INT NOT NULL CONSTRAINT DF_Penalties_CreatedBy DEFAULT (1);
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModifiedBy' AND Object_ID=Object_ID('Penalties'))
BEGIN ALTER TABLE Penalties ADD ModifiedBy INT NULL; END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModificationDate' AND Object_ID=Object_ID('Penalties'))
BEGIN ALTER TABLE Penalties ADD ModificationDate DATETIME NULL; END
GO
DELETE FROM dbo.Penalties WHERE NOT EXISTS (  SELECT 1  FROM dbo.PenaltiesList pl  WHERE pl.PenaltyCode = dbo.Penalties.PenaltyCode);
go
DELETE p FROM dbo.Penalties p WHERE NOT EXISTS (  SELECT 1  FROM dbo.Employees e WHERE e.EmployeeCode = p.EmployeeCode);
go

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.PenaltiesList SET (LOCK_ESCALATION = TABLE)
COMMIT
BEGIN TRANSACTION

ALTER TABLE dbo.Employees SET (LOCK_ESCALATION = TABLE)
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Penalties ADD CONSTRAINT FK_Penalties_Employees FOREIGN KEY	(	EmployeeCode) REFERENCES dbo.Employees(EmployeeCode) ON UPDATE  NO ACTION  ON DELETE  NO ACTION 
ALTER TABLE dbo.Penalties ADD CONSTRAINT FK_Penalties_PenaltiesList FOREIGN KEY(PenaltyCode) REFERENCES dbo.PenaltiesList(PenaltyCode) ON UPDATE  NO ACTION  ON DELETE  NO ACTION 
ALTER TABLE dbo.Penalties ADD CONSTRAINT FK_Penalties_SecurityUserCreatedBy FOREIGN KEY (CreatedBy) REFERENCES dbo.SecurityUser(SecurityUserCode) ON UPDATE  NO ACTION  ON DELETE  NO ACTION 
ALTER TABLE dbo.Penalties ADD CONSTRAINT FK_Penalties_SecurityUserModifiedBy FOREIGN KEY(ModifiedBy) REFERENCES dbo.SecurityUser(SecurityUserCode) ON UPDATE  NO ACTION ON DELETE  NO ACTION 
ALTER TABLE dbo.Penalties SET (LOCK_ESCALATION = TABLE)
COMMIT
GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='PenaltiesSalary' AND Object_ID=OBJECT_ID('Penalties')) ALTER TABLE Penalties ADD PenaltiesSalary FLOAT NULL CONSTRAINT DF_Penalties_PenaltiesSalary DEFAULT ((0));
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='Approved' AND Object_ID=OBJECT_ID('Penalties')) ALTER TABLE Penalties ADD Approved TINYINT NULL CONSTRAINT DF_Penalties_Approved DEFAULT ((0));
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='DetailsFrom' AND Object_ID=OBJECT_ID('Penalties')) ALTER TABLE Penalties ADD DetailsFrom FLOAT NULL CONSTRAINT DF_Penalties_DetailsFrom DEFAULT ((0));
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='DetailsTo' AND Object_ID=OBJECT_ID('Penalties')) ALTER TABLE Penalties ADD DetailsTo FLOAT NULL CONSTRAINT DF_Penalties_DetailsTo DEFAULT ((0));
GO
IF EXISTS (SELECT 1 FROM sys.columns    WHERE Name = 'Colsed' AND Object_ID = Object_ID('dbo.Penalties'))
BEGIN  ALTER TABLE dbo.Penalties DROP COLUMN Colsed;
END

GO
 INSERT[dbo].[ConfigParameter]  ([ConfigKey], [Name], [Arabic_Name], [ConfigValue], [ConfigCategoryCode], [OrderNo], [ConfigType], [IsVisible], [ModifiedBy], [ModificationDate], [RequestTypeId],[ConfigDescription])
VALUES(N'HR_NumberOfMonthsForThePenalty',N'Number Of Months For The Penalty',N'عدد شهور سقوط الجزاء',N'6',15,3,2,1,Null,Null,Null,N'Number Of Months For The Penalty To Lapse')
Go

----------------------------------------- End 2025-12-10 -----------------


----------------------------------------- ’Mostafa 2025-12-14 -----------------

BEGIN TRANSACTION;
SET QUOTED_IDENTIFIER ON;
SET ARITHABORT ON;
SET NUMERIC_ROUNDABORT OFF;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;

DECLARE @s NVARCHAR(MAX)='';

-- Drop FK coming from other tables (incoming)
SELECT @s += 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(fk.parent_object_id) + '].[' + OBJECT_NAME(fk.parent_object_id) + '] DROP CONSTRAINT [' + fk.name + ']; '
FROM sys.foreign_keys fk
WHERE fk.referenced_object_id = OBJECT_ID('dbo.LeaveTransaction');

-- Drop FK inside this table (outing)
SELECT @s += 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']; '
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID('dbo.LeaveTransaction');

-- Drop PK, UQ, CHECK, DEFAULT
SELECT @s += 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']; '
FROM sys.objects
WHERE parent_object_id = OBJECT_ID('dbo.LeaveTransaction') AND type IN('PK','UQ','C','D');

EXEC(@s);

-- Drop temp table if exists
IF OBJECT_ID(N'dbo.Tmp_LeaveTransaction', 'U') IS NOT NULL
DROP TABLE dbo.Tmp_LeaveTransaction;

CREATE TABLE dbo.Tmp_LeaveTransaction
(
LeaveTransactionCode int NOT NULL IDENTITY (1, 1),
Serial float(53) NOT NULL,
EmployeeCode int NOT NULL,
VacationCode tinyint NULL,
FromDate datetime NOT NULL,
ToDate datetime NULL,
FromDateHijri nvarchar(10) NULL,
ToDateHijri nvarchar(10) NULL,
Comment nvarchar(250) NULL,
DaysCount float(53) NULL,
AddBy nvarchar(200) NULL,
AddDate datetime NULL,
LastChangeBy nvarchar(200) NULL,
ChangeDate datetime NULL,
VacationRequestSerial float(53) NULL,
MainType tinyint NULL,
[Return] bit NULL,
RequiredReturnNotification bit NULL,
FCClosed bit NULL,
VacationStatusCode int NULL
)  ON [PRIMARY]

ALTER TABLE dbo.Tmp_LeaveTransaction SET (LOCK_ESCALATION = TABLE)

ALTER TABLE dbo.Tmp_LeaveTransaction ADD CONSTRAINT	DF_LeaveTransaction_Serial DEFAULT ((0)) FOR Serial

ALTER TABLE dbo.Tmp_LeaveTransaction ADD CONSTRAINT	DF_LeaveTransaction_EmployeeCode DEFAULT ((0)) FOR EmployeeCode

ALTER TABLE dbo.Tmp_LeaveTransaction ADD CONSTRAINT	DF_LeaveTransaction_VacationCode DEFAULT ((0)) FOR VacationCode

ALTER TABLE dbo.Tmp_LeaveTransaction ADD CONSTRAINT	DF_LeaveTransaction_Return DEFAULT ((0)) FOR [Return]

ALTER TABLE dbo.Tmp_LeaveTransaction ADD CONSTRAINT	DF_LeaveTransaction_RequiredReturnNotification DEFAULT ((0)) FOR RequiredReturnNotification

ALTER TABLE dbo.Tmp_LeaveTransaction ADD CONSTRAINT	DF_LeaveTransaction_FCClosed DEFAULT ((0)) FOR FCClosed

ALTER TABLE dbo.Tmp_LeaveTransaction ADD CONSTRAINT	DF_LeaveTransaction_MainType DEFAULT ((0)) FOR MainType

ALTER TABLE dbo.Tmp_LeaveTransaction ADD CONSTRAINT	DF_LeaveTransaction_VacationStatusCode DEFAULT ((0)) FOR VacationStatusCode

-- Note: No need for IDENTITY_INSERT since we're not inserting into the new identity column
-- The identity column will auto-generate new values

IF EXISTS(SELECT * FROM dbo.LeaveTransaction)
EXEC('INSERT INTO dbo.Tmp_LeaveTransaction (Serial, EmployeeCode, VacationCode, FromDate, ToDate, FromDateHijri, ToDateHijri, Comment, DaysCount, AddBy, AddDate, LastChangeBy, ChangeDate, VacationRequestSerial, MainType, [Return], RequiredReturnNotification, FCClosed, VacationStatusCode)
SELECT Serial, EmployeeCode, VacationCode, FromDate, ToDate, FromDateHijri, ToDateHijri, Comment, DaysCount, AddBy, AddDate, LastChangeBy, ChangeDate, VacationRequestSerial, MainType, [Return], RequiredReturnNotification, FCClosed, VacationStatusCode FROM dbo.LeaveTransaction WITH (HOLDLOCK TABLOCKX)')

DROP TABLE dbo.LeaveTransaction

EXECUTE sp_rename N'dbo.Tmp_LeaveTransaction', N'LeaveTransaction', 'OBJECT' 

ALTER TABLE dbo.LeaveTransaction ADD CONSTRAINT
PK_LeaveTransaction PRIMARY KEY CLUSTERED (LeaveTransactionCode) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

COMMIT

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ADaysCount' AND Object_ID=Object_ID('LeaveTransaction'))
BEGIN
ALTER TABLE LeaveTransaction ADD ADaysCount  float(53) NULL CONSTRAINT DF_LeaveTransaction_ADaysCount DEFAULT (0);
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='PenaltySerial' AND Object_ID=Object_ID('LeaveTransaction'))
BEGIN
ALTER TABLE LeaveTransaction ADD PenaltySerial  float(53) NULL CONSTRAINT DF_LeaveTransaction_PenaltySerial DEFAULT (0);
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreationDate' AND Object_ID=Object_ID('LeaveTransaction'))
BEGIN
ALTER TABLE LeaveTransaction ADD CreationDate DATETIME NOT NULL CONSTRAINT DF_LeaveTransaction_CreationDate DEFAULT (GETDATE());
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreatedBy' AND Object_ID=Object_ID('LeaveTransaction'))
BEGIN
ALTER TABLE LeaveTransaction ADD CreatedBy INT NOT NULL CONSTRAINT DF_LeaveTransaction_CreatedBy DEFAULT (1);
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModifiedBy' AND Object_ID=Object_ID('LeaveTransaction'))
BEGIN ALTER TABLE LeaveTransaction ADD ModifiedBy INT NULL; END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModificationDate' AND Object_ID=Object_ID('LeaveTransaction'))
BEGIN ALTER TABLE LeaveTransaction ADD ModificationDate DATETIME NULL; END
GO
UPDATE dbo.LeaveTransaction  SET CreationDate = AddDate  WHERE AddDate IS NOT NULL;
GO
UPDATE dbo.LeaveTransaction  SET ModificationDate = ChangeDate  WHERE ChangeDate IS NOT NULL
GO
IF EXISTS (SELECT 1 FROM sys.columns    WHERE Name = 'AddBy' AND Object_ID = Object_ID('dbo.LeaveTransaction'))
BEGIN
ALTER TABLE dbo.LeaveTransaction DROP COLUMN AddBy;
END

IF EXISTS (SELECT 1 FROM sys.columns   WHERE Name = 'AddDate' AND Object_ID = Object_ID('dbo.LeaveTransaction'))
BEGIN
ALTER TABLE dbo.LeaveTransaction DROP COLUMN AddDate;
END

IF EXISTS (SELECT 1 FROM sys.columns     WHERE Name = 'LastChangeBy' AND Object_ID = Object_ID('dbo.LeaveTransaction'))
BEGIN
ALTER TABLE dbo.LeaveTransaction DROP COLUMN LastChangeBy;
END

IF EXISTS (SELECT 1 FROM sys.columns   WHERE Name = 'ChangeDate' AND Object_ID = Object_ID('dbo.LeaveTransaction'))
BEGIN
ALTER TABLE dbo.LeaveTransaction DROP COLUMN ChangeDate;
END

IF EXISTS (SELECT 1 FROM sys.columns  WHERE Name = 'MDT' AND Object_ID = Object_ID('dbo.LeaveTransaction'))
BEGIN
ALTER TABLE dbo.LeaveTransaction DROP COLUMN MDT;
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.default_constraints dc  INNER JOIN sys.columns c
        ON dc.parent_object_id = c.object_id   AND dc.parent_column_id = c.column_id  WHERE dc.parent_object_id = OBJECT_ID(N'dbo.LeaveTransaction')   AND c.name = N'MainType')
BEGIN
    ALTER TABLE dbo.LeaveTransaction ADD CONSTRAINT DF_LeaveTransaction_MainType  DEFAULT (0) FOR MainType;
END
GO

IF NOT EXISTS (SELECT 1 FROM sys.default_constraints dc  INNER JOIN sys.columns c
        ON dc.parent_object_id = c.object_id   AND dc.parent_column_id = c.column_id  WHERE dc.parent_object_id = OBJECT_ID(N'dbo.LeaveTransaction')   AND c.name = N'VacationStatusCode ')
BEGIN
    ALTER TABLE dbo.LeaveTransaction ADD CONSTRAINT DF_LeaveTransaction_VacationStatusCode   DEFAULT (0) FOR VacationStatusCode ;
END

go 
DELETE FROM LeaveTransaction WHERE NOT EXISTS ( SELECT 1   FROM Employees vt   WHERE vt.EmployeeCode = LeaveTransaction.EmployeeCode);
DELETE FROM LeaveTransaction WHERE NOT EXISTS ( SELECT 1   FROM VacationType vt   WHERE vt.VacationCode = LeaveTransaction.VacationCode);
Go
BEGIN TRANSACTION;
SET QUOTED_IDENTIFIER ON;
SET ARITHABORT ON;
SET NUMERIC_ROUNDABORT OFF;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;

ALTER TABLE dbo.VacationType SET (LOCK_ESCALATION = TABLE);
ALTER TABLE dbo.Employees SET (LOCK_ESCALATION = TABLE);
ALTER TABLE dbo.SecurityUser SET (LOCK_ESCALATION = TABLE);

ALTER TABLE dbo.LeaveTransaction ADD CONSTRAINT FK_LeaveTransaction_Employees FOREIGN KEY(EmployeeCode) REFERENCES dbo.Employees(EmployeeCode) ON UPDATE NO ACTION ON DELETE NO ACTION;
ALTER TABLE dbo.LeaveTransaction  ADD CONSTRAINT FK_LeaveTransaction_SecurityUserCreatedBy  FOREIGN KEY (CreatedBy)  REFERENCES dbo.SecurityUser(SecurityUserCode)  ON UPDATE NO ACTION  ON DELETE NO ACTION;
ALTER TABLE dbo.LeaveTransaction  ADD CONSTRAINT FK_LeaveTransaction_SecurityUserModifiedBy  FOREIGN KEY (ModifiedBy)  REFERENCES dbo.SecurityUser(SecurityUserCode)  ON UPDATE NO ACTION  ON DELETE NO ACTION;
ALTER TABLE dbo.LeaveTransaction  ADD CONSTRAINT FK_LeaveTransaction_VacationType  FOREIGN KEY (VacationCode)  REFERENCES dbo.VacationType(VacationCode)  ON UPDATE NO ACTION  ON DELETE NO ACTION;
ALTER TABLE dbo.LeaveTransaction SET (LOCK_ESCALATION = TABLE);

COMMIT TRANSACTION;
GO
IF OBJECT_ID(N'dbo.VacationSpecialBalance', N'U') IS NULL
BEGIN
CREATE TABLE [dbo].[VacationSpecialBalance](
[Serial] [int] NOT NULL,
[EmployeeCode] [int] NULL,
[PYear] [int] NULL,
[Period] [tinyint] NULL,
[Balance] [float] NULL,
[VacationCollectCode] [tinyint] NULL,
[Reason] [nvarchar](200) NULL,
[AddBy] [nvarchar](20) NULL,
[AddDate] [datetime] NULL,
[LastChangeBy] [nvarchar](20) NULL,
[ChangeDate] [datetime] NULL,
[VacationEffectCode] [tinyint] NULL,
[TType] [int] NULL,
[SpecialDate] [datetime] NULL,
[SpecialDateHijri] [nvarchar](10) NULL,
[MDT] [datetime] NULL,
[ImportNumber] [int] NULL,
[WebRequestType] [float] NULL,
[WebRequestId] [float] NULL,
[LinkSerial] [float] NULL,
CONSTRAINT [PK_VacationSpecialBalance] PRIMARY KEY CLUSTERED 
(
[Serial] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Periods' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
CREATE TABLE [dbo].[Periods](
[Period] [tinyint] NOT NULL,
[Name] [nvarchar](20) NULL,
[Arabic_Name] [nvarchar](20) NULL,
[TransactionDayes] [tinyint] NULL,
[MonthDays] [int] NULL,
[MDT] [datetime] NULL,
[TransactionDays] [tinyint] NULL,
CONSTRAINT [PK_Periods] PRIMARY KEY CLUSTERED ([Period]));
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'VacationCollectTypes' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
CREATE TABLE [dbo].[VacationCollectTypes](
[VacationCollectCode] [tinyint] NOT NULL,
[Name] [nvarchar](30) NULL,
[Arabic_Name] [nvarchar](30) NULL,
[MDT] [datetime] NULL,
CONSTRAINT PK_VacationCollectTypes PRIMARY KEY (VacationCollectCode));
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'VacationEffect' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
CREATE TABLE [dbo].[VacationEffect](
[VacationEffectCode] [tinyint] NOT NULL,
[Name] [nvarchar](50) NULL,
[Arabic_Name] [nvarchar](50) NULL,
[MDT] [datetime] NULL,
CONSTRAINT PK_VacationEffect PRIMARY KEY (VacationEffectCode));
END
GO

/* ================================
2) INSERT PERIODS (IF NOT EXISTS)
================================ */
INSERT INTO dbo.Periods (Period, Name, Arabic_Name, TransactionDayes, MonthDays, MDT) SELECT v.*FROM (VALUES
(1 ,N'January'  ,N'يناير'   ,31,31,'2021-03-25'),
(2 ,N'February' ,N'فبراير'  ,31,28,'2021-03-25'),
(3 ,N'March'    ,N'مارس'    ,28,31,'2021-03-25'),
(4 ,N'April'    ,N'أبريل'   ,31,30,'2021-03-25'),
(5 ,N'May'      ,N'مايو'    ,30,31,'2021-03-25'),
(6 ,N'June'     ,N'يونيو'   ,31,30,'2021-03-25'),
(7 ,N'July'     ,N'يوليو'   ,30,31,'2021-03-25'),
(8 ,N'August'   ,N'أغسطس'   ,31,31,'2021-03-25'),
(9 ,N'September',N'سبتمبر'  ,31,30,'2021-03-25'),
(10,N'October'  ,N'أكتوبر'  ,30,31,'2021-03-25'),
(11,N'November' ,N'نوفمبر'  ,31,30,'2021-03-25'),
(12,N'December' ,N'ديسمبر'  ,30,31,'2021-03-25')
) v(Period, Name, Arabic_Name, TransactionDayes, MonthDays, MDT) WHERE NOT EXISTS (SELECT 1 FROM dbo.Periods p WHERE p.Period = v.Period);
GO

/* ================================
4) INSERT VACATION EFFECT (SAFE)
================================ */
INSERT INTO dbo.VacationEffect (VacationEffectCode, Name, Arabic_Name, MDT)
SELECT v.*
FROM (VALUES
(1,N'Discount',N'الخصم','2019-11-10'),
(2,N'Due',N'المستحق','2019-11-10'),
(3,N'Consumption',N'المستهلك','2019-11-10')
) v(VacationEffectCode, Name, Arabic_Name, MDT)WHERE NOT EXISTS (SELECT 1 FROM dbo.VacationEffect e WHERE e.VacationEffectCode = v.VacationEffectCode);
GO

IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'VacationSpecialBalance' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
CREATE TABLE [dbo].[VacationSpecialBalance](
	[Serial] [int] NULL,
	[EmployeeCode] [int] NULL,
	[PYear] [int] NULL,
	[Period] [tinyint] NULL,
	[Balance] [float] NULL,
	[VacationCollectCode] [tinyint] NULL,
	[Reason] [nvarchar](200) NULL,
	[AddBy] [nvarchar](20) NULL,
	[AddDate] [datetime] NULL,
	[LastChangeBy] [nvarchar](20) NULL,
	[ChangeDate] [datetime] NULL,
	[VacationEffectCode] [tinyint] NULL,
	[TType] [int] NULL,
	[SpecialDate] [datetime] NULL,
	[SpecialDateHijri] [nvarchar](10) NULL,
	[MDT] [datetime] NULL,
	[ImportNumber] [int] NULL,
	[WebRequestType] [float] NULL,
	[WebRequestId] [float] NULL,
	[LinkSerial] [float] NULL
) ON [PRIMARY]
END
GO
IF COL_LENGTH('VacationType', 'WF_IsMultiSelect') IS NOT NULL
    UPDATE VacationType SET WF_IsMultiSelect = ISNULL(WF_IsMultiSelect, 0);
	GO
IF COL_LENGTH('VacationType', 'InsideVacationPlan') IS NOT NULL
    UPDATE VacationType SET InsideVacationPlan = ISNULL(InsideVacationPlan, 0);
	GO
IF COL_LENGTH('VacationType', 'HaveDifferentTrack') IS NOT NULL
    UPDATE VacationType SET HaveDifferentTrack = ISNULL(HaveDifferentTrack, 0);
	GO
IF COL_LENGTH('VacationType', 'EffectInAppraisal') IS NOT NULL
    UPDATE VacationType SET EffectInAppraisal = ISNULL(EffectInAppraisal, 0);
	GO
IF COL_LENGTH('VacationType', 'ShowVacationTime') IS NOT NULL
    UPDATE VacationType SET ShowVacationTime = ISNULL(ShowVacationTime, 0);
	GO
IF COL_LENGTH('VacationType', 'ShowSalaryAdvanceRequired') IS NOT NULL
    UPDATE VacationType SET ShowSalaryAdvanceRequired = ISNULL(ShowSalaryAdvanceRequired, 0);
	GO
IF COL_LENGTH('VacationType', 'ShowAirTicketRequired') IS NOT NULL
    UPDATE VacationType SET ShowAirTicketRequired = ISNULL(ShowAirTicketRequired, 0);
	GO
IF COL_LENGTH('VacationType', 'CanSplitVacation') IS NOT NULL
    UPDATE VacationType SET CanSplitVacation = ISNULL(CanSplitVacation, 0);
	GO
IF COL_LENGTH('VacationType', 'IncludeWorkingHour') IS NOT NULL
    UPDATE VacationType SET IncludeWorkingHour = ISNULL(IncludeWorkingHour, 0);
	GO
IF COL_LENGTH('VacationType', 'ReplacementEmployeeRequired') IS NOT NULL
    UPDATE VacationType SET ReplacementEmployeeRequired = ISNULL(ReplacementEmployeeRequired, 0);
	GO
IF COL_LENGTH('VacationType', 'VacationsPlan') IS NOT NULL
    UPDATE VacationType SET VacationsPlan = ISNULL(VacationsPlan, 0);
	GO
IF COL_LENGTH('VacationType', 'DeductThisDaysInAirTicket') IS NOT NULL
    UPDATE VacationType SET DeductThisDaysInAirTicket = ISNULL(DeductThisDaysInAirTicket, 0);
	GO
IF COL_LENGTH('VacationType', 'AbsentPenalty') IS NOT NULL
    UPDATE VacationType SET AbsentPenalty = ISNULL(AbsentPenalty, 0);
	GO
IF COL_LENGTH('VacationType', 'AffectSCBonus') IS NOT NULL
    UPDATE VacationType SET AffectSCBonus = ISNULL(AffectSCBonus, 0);
	GO
IF COL_LENGTH('VacationType', 'UseProfileSpecialRate') IS NOT NULL
    UPDATE VacationType SET UseProfileSpecialRate = ISNULL(UseProfileSpecialRate, 0);
	GO
IF COL_LENGTH('VacationType', 'UseTimePenaltiesActions') IS NOT NULL
    UPDATE VacationType SET UseTimePenaltiesActions = ISNULL(UseTimePenaltiesActions, 0);
	GO
IF COL_LENGTH('VacationType', 'DuplicateCalculate') IS NOT NULL
    UPDATE VacationType SET DuplicateCalculate = ISNULL(DuplicateCalculate, 0);
	GO
IF COL_LENGTH('VacationType', 'ConvertCountOfDaysTo30') IS NOT NULL
    UPDATE VacationType SET ConvertCountOfDaysTo30 = ISNULL(ConvertCountOfDaysTo30, 0);
	GO
IF COL_LENGTH('VacationType', 'HideInLeavesAndVacationsScreen') IS NOT NULL
    UPDATE VacationType SET HideInLeavesAndVacationsScreen = ISNULL(HideInLeavesAndVacationsScreen, 0);
	GO
IF COL_LENGTH('VacationType', 'CantAddRequestForEmpHaveFingerPrint') IS NOT NULL
    UPDATE VacationType SET CantAddRequestForEmpHaveFingerPrint = ISNULL(CantAddRequestForEmpHaveFingerPrint, 0);
	GO
IF COL_LENGTH('VacationType', 'DisplayInMovementVacations') IS NOT NULL
    UPDATE VacationType SET DisplayInMovementVacations = ISNULL(DisplayInMovementVacations, 0);
	GO
IF COL_LENGTH('VacationType', 'IsMissionPlace') IS NOT NULL
    UPDATE VacationType SET IsMissionPlace = ISNULL(IsMissionPlace, 0);
	GO
IF COL_LENGTH('VacationType', 'ShowVisaRequired') IS NOT NULL
    UPDATE VacationType SET ShowVisaRequired = ISNULL(ShowVisaRequired, 0);
	GO
	-- Sequence
IF NOT EXISTS (
    SELECT 1 FROM sys.columns 
    WHERE name = N'Sequence' AND object_id = OBJECT_ID(N'dbo.VacationType')
)
BEGIN
    ALTER TABLE dbo.VacationType
    ADD Sequence INT NOT NULL CONSTRAINT DF_VacationType_Sequence DEFAULT (0);
END
GO

-- MaxDaysPerWeek
IF NOT EXISTS (
    SELECT 1 FROM sys.columns 
    WHERE name = N'MaxDaysPerWeek' AND object_id = OBJECT_ID(N'dbo.VacationType')
)
BEGIN
    ALTER TABLE dbo.VacationType
    ADD MaxDaysPerWeek INT NOT NULL CONSTRAINT DF_VacationType_MaxDaysPerWeek DEFAULT (0);
END
GO

-- DayStartOfWeekCode
IF NOT EXISTS (
    SELECT 1 FROM sys.columns 
    WHERE name = N'DayStartOfWeekCode' AND object_id = OBJECT_ID(N'dbo.VacationType')
)
BEGIN
    ALTER TABLE dbo.VacationType
    ADD DayStartOfWeekCode INT NOT NULL CONSTRAINT DF_VacationType_DayStartOfWeekCode DEFAULT (1);
END
GO

IF COL_LENGTH('VacationType', 'ReferenceToCalculateForTerminatedUntilEndOfMonth') IS NOT NULL
    UPDATE VacationType SET ReferenceToCalculateForTerminatedUntilEndOfMonth = ISNULL(ReferenceToCalculateForTerminatedUntilEndOfMonth, 0);
	GO
IF NOT EXISTS (SELECT 1 FROM sys.default_constraints dc  INNER JOIN sys.columns c
        ON dc.parent_object_id = c.object_id   AND dc.parent_column_id = c.column_id  WHERE dc.parent_object_id = OBJECT_ID(N'dbo.VacationType')   AND c.name = N'ReferenceToCalculateForTerminatedUntilEndOfMonth')
BEGIN
    ALTER TABLE dbo.VacationType ADD CONSTRAINT DF_VacationType_ReferenceToCalculateForTerminatedUntilEndOfMonth  DEFAULT (0) FOR ReferenceToCalculateForTerminatedUntilEndOfMonth;
END
GO
IF COL_LENGTH('VacationType', 'DiffrentEffectAccrodingToPayCode') IS NOT NULL
    UPDATE VacationType SET DiffrentEffectAccrodingToPayCode = ISNULL(DiffrentEffectAccrodingToPayCode, 0);
	GO
IF NOT EXISTS (SELECT 1 FROM sys.default_constraints dc  INNER JOIN sys.columns c
        ON dc.parent_object_id = c.object_id   AND dc.parent_column_id = c.column_id  WHERE dc.parent_object_id = OBJECT_ID(N'dbo.VacationType')   AND c.name = N'DiffrentEffectAccrodingToPayCode')
BEGIN
    ALTER TABLE dbo.VacationType ADD CONSTRAINT DF_VacationType_DiffrentEffectAccrodingToPayCode  DEFAULT (0) FOR DiffrentEffectAccrodingToPayCode;
END
GO
IF COL_LENGTH('VacationType', 'DropDayoffBalance') IS NOT NULL
    UPDATE VacationType SET DropDayoffBalance = ISNULL(DropDayoffBalance, 0);
	GO
IF NOT EXISTS (SELECT 1 FROM sys.default_constraints dc  INNER JOIN sys.columns c
        ON dc.parent_object_id = c.object_id   AND dc.parent_column_id = c.column_id  WHERE dc.parent_object_id = OBJECT_ID(N'dbo.VacationType')   AND c.name = N'DropDayoffBalance')
BEGIN
    ALTER TABLE dbo.VacationType ADD CONSTRAINT DF_VacationType_DropDayoffBalance  DEFAULT (0) FOR DropDayoffBalance;
END
GO
IF COL_LENGTH('VacationType', 'ForReadOnlyInAttendanceSystem') IS NOT NULL
    UPDATE VacationType SET ForReadOnlyInAttendanceSystem = ISNULL(ForReadOnlyInAttendanceSystem, 0);
	GO

IF NOT EXISTS (SELECT 1 FROM sys.default_constraints dc  INNER JOIN sys.columns c
        ON dc.parent_object_id = c.object_id   AND dc.parent_column_id = c.column_id  WHERE dc.parent_object_id = OBJECT_ID(N'dbo.VacationType')   AND c.name = N'ForReadOnlyInAttendanceSystem')
BEGIN
    ALTER TABLE dbo.VacationType ADD CONSTRAINT DF_VacationType_ForReadOnlyInAttendanceSystem  DEFAULT (0) FOR ForReadOnlyInAttendanceSystem;
END
GO


----------------------------------------- End 2025-12-14 -----------------

INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1217,N'Funcations',N'المهام',27,NULL,NULL,N'fa-solid fa-list-check',NULL,2,1,1)
Go
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1217,22)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4312,N'المهام',N'Funcations',1217,1)
Go
INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1218,N'Human Resource',N'شئون العاملين',1217,NULL,NULL,N'fa-solid fa-users-gear',NULL,1,1,1)
Go
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1218,22)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4313,N'شئون العاملين',N'Human Resource',1218,1)
Go
INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1221,N'Penalties',N'الجزاءات',1218,N'/Personal/Penalties',N'Index',N'fa-solid fa-users-slash',N'get',1,1,1)
Go
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1221,22)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4315,N'الجزاءات',N'Penalties',1221,1)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4316,N'عرض الجزاءات',N'View Penalties',1221,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4317,N'إضافة الجزاءات',N'Add Penalties',1221,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4318,N'تعديل الجزاءات',N'Edit Penalties',1221,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4319,N'حذف الجزاءات',N'Delete Penalties',1221,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4320,N'حفظ الجزاءات',N'Save Penalties',1221,2)
Go

INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1219,N'Leaves And Vacations',N'العطلات والاجازات',1217,NULL,NULL,N'fa-solid fa-plane-departure',NULL,2,1,1)
Go
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1219,22)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4314,N'العطلات والاجازات',N'Leaves And Vacations',1219,1)
Go
INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1222,N'Leaves And Vacations',N'الغياب والاجازات',1219,N'/Personal/LeaveTransaction',N'Index',N'fa-solid fa-umbrella-beach',N'get',1,1,1)
Go
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1222,22)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4321,N'الغياب والاجازات',N'Leaves And Vacations',1222,1)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4322,N'عرض الغياب والاجازات',N'View Leaves And Vacations',1222,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4323,N'إضافة الغياب والاجازات',N'Add Leaves And Vacations',1222,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4324,N'تعديل الغياب والاجازات',N'Edit Leaves And Vacations',1222,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4325,N'حذف الغياب والاجازات',N'Delete Leaves And Vacations',1222,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4326,N'حفظ الغياب والاجازات',N'Save Leaves And Vacations',1222,2)

GO



----------------------------------------- Ali 2025-12-21 -----------------

 INSERT[dbo].[ConfigParameter]  ([ConfigKey], [Name], [Arabic_Name], [ConfigValue], [ConfigCategoryCode], [OrderNo], [ConfigType], [IsVisible], [ModifiedBy], [ModificationDate], [RequestTypeId],[ConfigDescription])
VALUES(N'SyncDataFromERPToPayroll',N'Sync Data From ERP To Payroll',N'مزامنة البيانات من ERP إلى Payroll',N'false',1,68,1,1,Null,Null,Null,N'مزامنة البيانات من ERP إلى Payroll')
Go

----------------------------------------- End 2025-12-21 -----------------
----------------------------------------- Mostafa 2025-12-23 -----------------
BEGIN TRANSACTION;
SET QUOTED_IDENTIFIER ON;
SET ARITHABORT ON;
SET NUMERIC_ROUNDABORT OFF;
SET CONCAT_NULL_YIELDS_NULL ON;
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;

DECLARE @s NVARCHAR(MAX)='';

-- Drop FK coming from other tables (incoming)
SELECT @s += 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(fk.parent_object_id) + '].[' + OBJECT_NAME(fk.parent_object_id) + '] DROP CONSTRAINT [' + fk.name + ']; '
FROM sys.foreign_keys fk
WHERE fk.referenced_object_id = OBJECT_ID('dbo.ResignationReasons');

-- Drop FK inside this table (outing)
SELECT @s += 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']; '
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID('dbo.ResignationReasons');

-- Drop PK, UQ, CHECK, DEFAULT
SELECT @s += 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']; '
FROM sys.objects
WHERE parent_object_id = OBJECT_ID('dbo.ResignationReasons') AND type IN('PK','UQ','C','D');

EXEC(@s);

-- Drop temp table if exists
IF OBJECT_ID(N'dbo.Tmp_ResignationReasons', 'U') IS NOT NULL
DROP TABLE dbo.Tmp_ResignationReasons;

CREATE TABLE dbo.Tmp_ResignationReasons
(
ResignationCode tinyint NOT NULL IDENTITY (0, 1),
Name nvarchar(150) NULL,
Arabic_Name nvarchar(150) NULL,
GeneralTerminatedCode int NULL,
NotCalculatedWithTurnover bit NULL,
HaveDifferentTrack bit NOT NULL,
HideFromRequest bit NOT NULL)  ON [PRIMARY]

ALTER TABLE dbo.Tmp_ResignationReasons SET (LOCK_ESCALATION = TABLE)

ALTER TABLE dbo.Tmp_ResignationReasons ADD CONSTRAINT	DF_ResignationReasons_NotCalculatedWithTurnover DEFAULT ((0)) FOR NotCalculatedWithTurnover

ALTER TABLE dbo.Tmp_ResignationReasons ADD CONSTRAINT DF_ResignationReasons_HaveDifferentTrack DEFAULT (CONVERT([bit],(0))) FOR HaveDifferentTrack

ALTER TABLE dbo.Tmp_ResignationReasons ADD CONSTRAINT DF_ResignationReasons_HideFromRequest DEFAULT (CONVERT([bit],(0))) FOR HideFromRequest

SET IDENTITY_INSERT dbo.Tmp_ResignationReasons ON

IF EXISTS(SELECT * FROM dbo.ResignationReasons)
EXEC('INSERT INTO dbo.Tmp_ResignationReasons (ResignationCode, Name, Arabic_Name, GeneralTerminatedCode, NotCalculatedWithTurnover, HaveDifferentTrack, HideFromRequest)
SELECT ResignationCode, Name, Arabic_Name, GeneralTerminatedCode, NotCalculatedWithTurnover, HaveDifferentTrack, HideFromRequest FROM dbo.ResignationReasons WITH (HOLDLOCK TABLOCKX)')

SET IDENTITY_INSERT dbo.Tmp_ResignationReasons OFF

IF OBJECT_ID(N'dbo.WF_ResignationRequest', 'U') IS NOT NULL
AND EXISTS ( SELECT 1   FROM sys.foreign_keys   WHERE name = N'FK_WF_ResignationRequest_ResignationReasons' AND parent_object_id = OBJECT_ID(N'dbo.WF_ResignationRequest'))

BEGIN
ALTER TABLE dbo.WF_ResignationRequest DROP CONSTRAINT FK_WF_ResignationRequest_ResignationReasons;
END


DROP TABLE dbo.ResignationReasons
EXECUTE sp_rename N'dbo.Tmp_ResignationReasons', N'ResignationReasons', 'OBJECT' 
ALTER TABLE dbo.ResignationReasons ADD CONSTRAINT PK_ResignationReasons PRIMARY KEY CLUSTERED (ResignationCode) 
WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
COMMIT

BEGIN TRANSACTION;
IF OBJECT_ID(N'dbo.WF_ResignationRequest', 'U') IS NOT NULL
AND OBJECT_ID(N'dbo.ResignationReasons', 'U') IS NOT NULL
AND NOT EXISTS (  SELECT 1   FROM sys.foreign_keys    WHERE name = N'FK_WF_ResignationRequest_ResignationReasons')
BEGIN

ALTER TABLE dbo.WF_ResignationRequest  ADD CONSTRAINT FK_WF_ResignationRequest_ResignationReasons   FOREIGN KEY (ResignationCode)  REFERENCES dbo.ResignationReasons (ResignationCode)  ON UPDATE NO ACTION ON DELETE NO ACTION;
END
ALTER TABLE dbo.WF_ResignationRequest SET (LOCK_ESCALATION = TABLE);
COMMIT
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CentralCode' AND Object_ID=Object_ID('ResignationReasons'))
BEGIN ALTER TABLE ResignationReasons ADD CentralCode INT NULL; END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='Serial' AND Object_ID=Object_ID('ResignationReasons'))
BEGIN ALTER TABLE ResignationReasons ADD Serial NVARCHAR(250) NULL; END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='Closed' AND Object_ID=Object_ID('ResignationReasons'))
BEGIN
ALTER TABLE ResignationReasons ADD Closed BIT NULL CONSTRAINT DF_ResignationReasons_Closed DEFAULT (0);
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreationDate' AND Object_ID=Object_ID('ResignationReasons'))
BEGIN
ALTER TABLE ResignationReasons ADD CreationDate DATETIME NOT NULL CONSTRAINT DF_ResignationReasons_CreationDate DEFAULT (GETDATE());
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreatedBy' AND Object_ID=Object_ID('ResignationReasons'))
BEGIN
ALTER TABLE ResignationReasons ADD CreatedBy INT NOT NULL CONSTRAINT DF_ResignationReasons_CreatedBy DEFAULT (1);
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModifiedBy' AND Object_ID=Object_ID('ResignationReasons'))
BEGIN ALTER TABLE ResignationReasons ADD ModifiedBy INT NULL; END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModificationDate' AND Object_ID=Object_ID('ResignationReasons'))
BEGIN ALTER TABLE ResignationReasons ADD ModificationDate DATETIME NULL; END
GO
IF EXISTS(SELECT 1 FROM ResignationReasons WHERE Serial IS NULL)
BEGIN
UPDATE ResignationReasons SET Serial = ResignationCode WHERE Serial IS NULL;
END
GO
UPDATE ResignationReasons SET Closed = 0WHERE Closed IS NULL;
GO

----------------------------------------- End 2025-12-23 -----------------

----------------------------------------- Mostafa 2025-12-24 -----------------
INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1226,N'Resignation',N'الاستقالة',1218,N'/Personal/EmployeeResignation',N'Index','fa-solid fa-user-large-slash',N'get',2,1,1)
Go
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1226,22)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4327,N'الاستقالة',N'Resignation',1226,1)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4328,N'عرض الاستقالة',N'View Resignation',1226,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4329,N'إضافة الاستقالة',N'Add Resignation',1226,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4330,N'تعديل الاستقالة',N'Edit Resignation',1226,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4332,N'حذف الاستقالة',N'Delete Resignation',1226,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4331,N'حفظ الاستقالة',N'Save Resignation',1226,2)
GO
----------------------------------------- End 2025-12-24 -----------------

----------------------------------------- Mostafa 2025-12-29 -----------------

 INSERT[dbo].[ConfigParameter]  ([ConfigKey], [Name], [Arabic_Name], [ConfigValue], [ConfigCategoryCode], [OrderNo], [ConfigType], [IsVisible], [ModifiedBy], [ModificationDate], [RequestTypeId],[ConfigDescription])
VALUES(N'HR_ResignedWithinTheMonth',N'Resigned Within The Month (Employee Resignation)',N'استقالت خلال الشهر (استقالة موظف)',N'False',15,4,1,1,Null,Null,Null,N'الموظفين الذين قاموا بتقديم استقالتهم أو تم قبول استقالتهم خلال الشهر الحالي')
Go
----------------------------------------- End 2025-12-29 -----------------
----------------------------------------- AK 2025-12-30 --------------------
INSERT INTO [SystemMenu](SystemMenuCode,Name,Arabic_Name,ParentID,ControllerName,ActionName,ImageURL,HTTPMethod,Sequence,IsVisible,ShowInPrivilegePage) VALUES (1227,N'Salary Increase and Change Status',N'زيادة الراتب وتغيير الحالة',1218,N'/Personal/Promotion',N'Index',N'fa fa-thumb-tack',N'get',3,1,1)
GO
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1227,22)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4333,N'زيادة الراتب وتغيير الحالة',N'Salary Increase and Change Status',1227,1)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4334,N'عرض زيادة الراتب وتغيير الحالة',N'View Salary Increase and Change Status',1227,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4335,N'إضافة زيادة الراتب وتغيير الحالة',N'Add Salary Increase and Change Status',1227,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4336,N'تعديل زيادة الراتب وتغيير الحالة',N'Edit Salary Increase and Change Status',1227,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4337,N'حذف زيادة الراتب وتغيير الحالة',N'Delete Salary Increase and Change Status',1227,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4338,N'حفظ زيادة الراتب وتغيير الحالة',N'Save Salary Increase and Change Status',1227,2)
GO
----------------------------------------- AK 2025-12-30 --------------------- 
----------------------------------------- Mostafa 2025-12-30 --------------------- 


INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1229,N'Keeping',N'العهد',1218,N'/Personal/Keeping',N'Index',N'fa-solid fa-box-open',N'get',4,1,1)
Go
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1229,22)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4345,N'العهد',N'Keeping',1229,1)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4346,N'عرض العهد',N'View Keeping',1229,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4347,N'إضافة العهد',N'Add Keeping',1229,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4348,N'تعديل العهد',N'Edit Keeping',1229,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4349,N'حذف العهد',N'Delete Keeping',1229,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4350,N'حفظ العهد',N'Save Keeping',1229,2)
GO

INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1228,N'Keeping Types',N'انواع العهد',1062,N'/Personal/KeepingType',N'Index',N'fa-solid fa-list-check',N'get',16,1,1)
Go
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1228,22)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4339,N'انواع العهد',N'Keeping Types',1228,1)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4340,N'عرض انواع العهد',N'View Keeping Types',1228,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4341,N'إضافة انواع العهد',N'Add Keeping Types',1228,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4342,N'تعديل انواع العهد',N'Edit Keeping Types',1228,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4343,N'حذف انواع العهد',N'Delete Keeping Types',1228,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4344,N'حفظ انواع العهد',N'Save Keeping Types',1228,2)
GO
 INSERT[dbo].[ConfigParameter]  ([ConfigKey], [Name], [Arabic_Name], [ConfigValue], [ConfigCategoryCode], [OrderNo], [ConfigType], [IsVisible], [ModifiedBy], [ModificationDate], [RequestTypeId],[ConfigDescription])
VALUES(N'HR_ResignationAllowedWithKeeping',N'Can A Resignation Be Submitted If There Is A Keeping?',N'يمكن تقديم الاستقالة في حالة وجود عهد ؟',N'False',15,5,1,1,Null,Null,Null,N'يمكن تقديم الاستقالة في حالة وجود عهد ؟')
Go
----------------------------------------- End 2025-12-30 --------------------- 
----------------------------------------- Mostafa 2026-01-01 --------------------- 
INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1230,N'Resignation Reasons',N'اسباب الاستقالة',1062,N'/Personal/ResignationReason',N'Index',N'fa-solid fa-triangle-exclamation',N'get',17,1,1)
Go
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1230,22)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4351,N'اسباب الاستقالة',N'Resignation Reasonss',1230,1)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4352,N'عرض اسباب الاستقالة',N'View Resignation Reasons',1230,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4353,N'إضافة اسباب الاستقالة',N'Add Resignation Reasons',1230,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4354,N'تعديل اسباب الاستقالة',N'Edit Resignation Reasons',1230,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4355,N'حذف اسباب الاستقالة',N'Delete Resignation Reasons',1230,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4356,N'حفظ اسباب الاستقالة',N'Save Resignation Reasons',1230,2)
GO

----------------------------------------- End 2026-01-01--------------------- 

----------------------------------------- Mostafa 2026-01-04--------------------- 

INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1231,N'Health Card',N'الشهادة الصحية',1218,N'/Personal/HealthCard',N'Index',N'fa-solid fa-hospital-user',N'get',5,1,1)
Go
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1231,22)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4357,N'الشهادة الصحية',N'Health Card',1231,1)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4358,N'عرض الشهادة الصحية',N'View Health Card',1231,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4359,N'إضافة الشهادة الصحية',N'Add Health Card',1231,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4360,N'تعديل الشهادة الصحية',N'Edit Health Card',1231,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4361,N'حذف الشهادة الصحية',N'Delete Health Card',1231,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4362,N'حفظ الشهادة الصحية',N'Save Health Card',1231,2)
GO
IF EXISTS (SELECT 1 FROM sys.columns  WHERE Name = 'MDT' AND Object_ID = Object_ID('dbo.HealthCard'))
BEGIN
ALTER TABLE dbo.HealthCard DROP COLUMN MDT;
END
GO
DELETE hc FROM dbo.HealthCard hc WHERE hc.EmployeeCode IS NULL OR NOT EXISTS ( SELECT 1  FROM dbo.Employees e WHERE e.EmployeeCode = hc.EmployeeCode);
GO
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION

DECLARE @s NVARCHAR(MAX)='';

-- Drop FK coming from other tables (incoming)
SELECT @s+= 'ALTER TABLE ['+OBJECT_SCHEMA_NAME(fk.parent_object_id)+'].['+OBJECT_NAME(fk.parent_object_id)+'] DROP CONSTRAINT ['+fk.name+']; '
FROM sys.foreign_keys fk
WHERE fk.referenced_object_id = OBJECT_ID('dbo.HealthCard');

-- Drop FK inside this table (outgoing)
SELECT @s+= 'ALTER TABLE ['+OBJECT_SCHEMA_NAME(parent_object_id)+'].['+OBJECT_NAME(parent_object_id)+'] DROP CONSTRAINT ['+name+']; '
FROM sys.foreign_keys
WHERE parent_object_id = OBJECT_ID('dbo.HealthCard');

-- Drop PK, UQ, CHECK, DEFAULT
SELECT @s+= 'ALTER TABLE ['+OBJECT_SCHEMA_NAME(parent_object_id)+'].['+OBJECT_NAME(parent_object_id)+'] DROP CONSTRAINT ['+name+']; '
FROM sys.objects
WHERE parent_object_id=OBJECT_ID('dbo.HealthCard')
AND type IN('PK','UQ','C','D');

EXEC(@s);

	IF OBJECT_ID(N'dbo.Tmp_HealthCard', 'U') IS NOT NULL
DROP TABLE dbo.Tmp_HealthCard;

CREATE TABLE dbo.Tmp_HealthCard(
	HealthCardCode int NOT NULL IDENTITY (1, 1),
	EmployeeCode int NOT NULL,
	CardNumber nvarchar(50) NULL,
	IssueDate datetime NULL,
	ExpireDate datetime NULL,
	IssuePlace nvarchar(150) NULL,
	IssueDateHijri nvarchar(10) NULL,
	ExpireDateHijri nvarchar(10) NULL,
	EndTrainingDate datetime NULL,
	EndTrainingDateHijri nvarchar(10) NULL,
	InvoiceNumber nvarchar(100) NULL,
	InvoiceDate datetime NULL,
	InvoiceDateHijri nvarchar(10) NULL
	)  ON [PRIMARY]

ALTER TABLE dbo.Tmp_HealthCard SET (LOCK_ESCALATION = TABLE)

SET IDENTITY_INSERT dbo.Tmp_HealthCard OFF

IF EXISTS(SELECT * FROM dbo.HealthCard)
	 EXEC('INSERT INTO dbo.Tmp_HealthCard (EmployeeCode, CardNumber, IssueDate, ExpireDate, IssuePlace, IssueDateHijri, ExpireDateHijri, EndTrainingDate, EndTrainingDateHijri, InvoiceNumber, InvoiceDate, InvoiceDateHijri)
		SELECT EmployeeCode, CardNumber, IssueDate, ExpireDate, IssuePlace, IssueDateHijri, ExpireDateHijri, EndTrainingDate, EndTrainingDateHijri, InvoiceNumber, InvoiceDate, InvoiceDateHijri FROM dbo.HealthCard WITH (HOLDLOCK TABLOCKX)')

DROP TABLE dbo.HealthCard

EXECUTE sp_rename N'dbo.Tmp_HealthCard', N'HealthCard', 'OBJECT' 

ALTER TABLE dbo.HealthCard ADD CONSTRAINT PK_HealthCard PRIMARY KEY CLUSTERED (HealthCardCode) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

COMMIT

GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreationDate' AND Object_ID=Object_ID('HealthCard'))
BEGIN
ALTER TABLE HealthCard ADD CreationDate DATETIME NOT NULL CONSTRAINT DF_HealthCard_CreationDate DEFAULT (GETDATE());
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreatedBy' AND Object_ID=Object_ID('HealthCard'))
BEGIN
ALTER TABLE HealthCard ADD CreatedBy INT NOT NULL CONSTRAINT DF_HealthCard_CreatedBy DEFAULT (1);
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModifiedBy' AND Object_ID=Object_ID('HealthCard'))
BEGIN ALTER TABLE HealthCard ADD ModifiedBy INT NULL; END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='Closed' AND Object_ID=Object_ID('HealthCard'))
BEGIN
ALTER TABLE HealthCard ADD Closed BIT NULL CONSTRAINT DF_HealthCard_Closed DEFAULT (0);
END
GO
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModificationDate' AND Object_ID=Object_ID('HealthCard'))
BEGIN ALTER TABLE HealthCard ADD ModificationDate DATETIME NULL; END
GO
ALTER TABLE dbo.HealthCard ADD CONSTRAINT FK_HealthCard_EmployeeCode FOREIGN KEY(EmployeeCode) REFERENCES dbo.Employees(EmployeeCode) ON UPDATE NO ACTION ON DELETE NO ACTION;
go
----------------------------------------- End 2026-01-04--------------------- 
----------------------------------------- AK 2026-01-05 --------------------
INSERT INTO [SystemMenu](SystemMenuCode,Name,Arabic_Name,ParentID,ControllerName,ActionName,ImageURL,HTTPMethod,Sequence,IsVisible,ShowInPrivilegePage) VALUES (1233,N'Change department status',N'تغيير الحالة الإدارية',1218,N'/Personal/ChangeDepartmentStatus',N'Index',N'fa fa-thumb-tack',N'get',1,1,1)
GO
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1233,22)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4369,N'تغيير الحالة الإدارية',N'Change department status',1233,1)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4370,N'عرض تغيير الحالة الإدارية',N'View Change department status',1233,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4371,N'إضافة تغيير الحالة الإدارية',N'Add Change department status',1233,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4372,N'تعديل تغيير الحالة الإدارية',N'Edit Change department status',1233,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4373,N'حذف تغيير الحالة الإدارية',N'Delete Change department status',1233,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4374,N'حفظ تغيير الحالة الإدارية',N'Save Change department status',1233,2)
GO
----------------------------------------- AK 2026-01-05 --------------------- 
----------------------------------------- Mostafa 2026-01-05 --------------------- 
INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1234,N'Contracts Renewal',N'تجديد العقد',1218,N'/Personal/ContractsRenewal',N'Index',N'fa-solid fa-rotate',N'get',6,1,1)
Go
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1234,22)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4375,N'تجديد العقد',N'Contracts Renewal',1234,1)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4376,N'عرض تجديد العقد',N'View Contracts Renewal',1234,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4377,N'إضافة تجديد العقد',N'Add Contracts Renewal',1234,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4378,N'تعديل تجديد العقد',N'Edit Contracts Renewal',1234,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4379,N'حذف تجديد العقد',N'Delete Contracts Renewal',1234,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4380,N'حفظ تجديد العقد',N'Save Contracts Renewal',1234,2)
GO
----------------------------------------- End 2026-01-05 --------------------- 
----------------------------------------- Shehab 2026-01-06 ---------------------

GO
INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], 
[ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES (1232, N'Marital Status', 
N'الحالة الاجتماعية', 1218, N'/Personal/ChangeMaritalStatus', N'Index', N'fas fa-wallet', N'get', 19, 1, 1)

Go
INSERT [dbo].[SystemMenuModule] ([SystemMenuCode], [AppModuleCode]) VALUES (1232, 22)
GO

Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4363,N'تغيير الحالة الاجتماعية',N'Change Marital Status',1232,1)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4364,N'عرض تعيير الحالة الاجتماعية',N'View Change Marital Status',1232,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4365,N'إضافة تغيير الحالة الاجتماعية',N'Add Change Marital Status',1232,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4366,N'تعديل تغيير الحالة الاجتماعية',N'Edit Change Marital Status',1232,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4367,N' حذف تغيير الحالة الاجتماعية ',N'Delete Change Marital Status',1232,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4368,N'حفظ تغيير الحالة الاجتماعية',N'Save Change Marital Status',1232,2)
GO

----------------------------------------- End 2026-01-06 ---------------------
----------------------------------------- Mostafa 2026-01-11 ---------------------

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4393,N'اختر جميع توثيق العقد',N'Select All Contract Documentation',1234,2)
GO
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4394,N'حفظ توثيق العقد',N'Save Contract Documentation',1234,2)



----------------------------------------- End 2026-01-11 ---------------------


----------------------------------------- AK 2026-01-11 --------------------
INSERT INTO [SystemMenu](SystemMenuCode,Name,Arabic_Name,ParentID,ControllerName,ActionName,ImageURL,HTTPMethod,Sequence,IsVisible,ShowInPrivilegePage) VALUES (1236,N'Health ID Card',N'البطاقة الصحية',1218,N'/Personal/EmployeeHealthIDCard',N'Index',N'fa fa-thumb-tack',N'get',1,1,1)
GO
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1236,22)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4387,N'البطاقة الصحية',N'Health ID Card',1236,1)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4388,N'عرض البطاقة الصحية',N'View Health ID Card',1236,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4389,N'إضافة البطاقة الصحية',N'Add Health ID Card',1236,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4390,N'تعديل البطاقة الصحية',N'Edit Health ID Card',1236,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4391,N'حذف البطاقة الصحية',N'Delete Health ID Card',1236,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4392,N'حفظ البطاقة الصحية',N'Save Health ID Card',1236,2)
GO
----------------------------------------- AK 2026-01-11 --------------------- 
----------------------------------------- Shehab 2026-01-11 --------------------
GO
INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], 
[ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES (1235, N'Lapor Office Receipt', 
N'بيانات كعب العمل', 1218, N'/Personal/LaporOfficeReceipt', N'Index', N'fas fa-receipt', N'get', 20, 1, 1)

Go
INSERT [dbo].[SystemMenuModule] ([SystemMenuCode], [AppModuleCode]) VALUES (1235, 22)
GO

Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4381,N'بيانات كعب العمل',N'Lapor Office Receipt',1235,1)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4382,N'عرض بيانات كعب العمل',N'View Lapor Office Receipt',1235,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4383,N'إضافة بيانات كعب العمل',N'Add Lapor Office Receipt',1235,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4384,N'تعديل بيانات كعب العمل',N'Edit Lapor Office Receipt',1235,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4385,N' حذف بيانات كعب العمل ',N'Delete Lapor Office Receipt',1235,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4386,N'حفظ بيانات كعب العمل',N'Save Lapor Office Receipt',1235,2)
GO


 delete from [WorkCube] where  [EmployeeCode] not in(select [EmployeeCode] from Employees)
  Go

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

COMMIT

BEGIN TRANSACTION
 
DECLARE @s NVARCHAR(MAX)='';
 
-- Drop FK coming from other tables (incoming)

SELECT @s+= 'ALTER TABLE ['+OBJECT_SCHEMA_NAME(fk.parent_object_id)+'].['+OBJECT_NAME(fk.parent_object_id)+'] DROP CONSTRAINT ['+fk.name+']; '

FROM sys.foreign_keys fk

WHERE fk.referenced_object_id = OBJECT_ID('dbo.WorkCube');
 
-- Drop FK inside this table (outgoing)

SELECT @s+= 'ALTER TABLE ['+OBJECT_SCHEMA_NAME(parent_object_id)+'].['+OBJECT_NAME(parent_object_id)+'] DROP CONSTRAINT ['+name+']; '

FROM sys.foreign_keys

WHERE parent_object_id = OBJECT_ID('dbo.WorkCube');
 
-- Drop PK, UQ, CHECK, DEFAULT

SELECT @s+= 'ALTER TABLE ['+OBJECT_SCHEMA_NAME(parent_object_id)+'].['+OBJECT_NAME(parent_object_id)+'] DROP CONSTRAINT ['+name+']; '

FROM sys.objects

WHERE parent_object_id=OBJECT_ID('dbo.WorkCube')

AND type IN('PK','UQ','C','D');
 
EXEC(@s);
 
	IF OBJECT_ID(N'dbo.Tmp_WorkCube', 'U') IS NOT NULL

DROP TABLE dbo.Tmp_WorkCube;
 
CREATE TABLE dbo.Tmp_WorkCube

	(

	WorkCubeCode int NOT NULL IDENTITY (1, 1),

	EmployeeCode int NOT NULL,

	CubeIssueDate datetime  NULL,

	CubeIssuePlace nvarchar(150)  NULL,

	CubeSavedDate datetime  NULL,

	CubeIssueNumber nvarchar(100)  NULL,

	CubeIssueDateHijri nvarchar(10) NULL,

	CubeSavedDateHijri nvarchar(10) NULL,

	MDT datetime NULL

	)  ON [PRIMARY]
 
ALTER TABLE dbo.Tmp_WorkCube SET (LOCK_ESCALATION = TABLE)
 
SET IDENTITY_INSERT dbo.Tmp_WorkCube OFF
 
IF EXISTS(SELECT * FROM dbo.WorkCube)

	 EXEC('INSERT INTO dbo.Tmp_WorkCube (EmployeeCode, CubeIssueDate, CubeIssuePlace, CubeSavedDate, CubeIssueNumber, CubeIssueDateHijri, CubeSavedDateHijri, MDT)

		SELECT EmployeeCode, CubeIssueDate, CubeIssuePlace, CubeSavedDate, CubeIssueNumber, CubeIssueDateHijri, CubeSavedDateHijri, MDT FROM dbo.WorkCube WITH (HOLDLOCK TABLOCKX)')
 
DROP TABLE dbo.WorkCube
 
EXECUTE sp_rename N'dbo.Tmp_WorkCube', N'WorkCube', 'OBJECT'
 
ALTER TABLE dbo.WorkCube ADD CONSTRAINT

	PK_WorkCube PRIMARY KEY CLUSTERED 

	(

	WorkCubeCode

	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 
 
	GO
	COMMIT
IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreationDate' AND Object_ID=Object_ID('WorkCube'))

BEGIN

ALTER TABLE WorkCube ADD CreationDate DATETIME NOT NULL CONSTRAINT DF_WorkCube_CreationDate DEFAULT (GETDATE());

END

GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreatedBy' AND Object_ID=Object_ID('WorkCube'))

BEGIN

ALTER TABLE WorkCube ADD CreatedBy INT NOT NULL CONSTRAINT DF_WorkCube_CreatedBy DEFAULT (1);

END

GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModifiedBy' AND Object_ID=Object_ID('WorkCube'))

BEGIN ALTER TABLE WorkCube ADD ModifiedBy INT NULL; END

GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModificationDate' AND Object_ID=Object_ID('WorkCube'))

BEGIN ALTER TABLE WorkCube ADD ModificationDate DATETIME NULL; 

END

go

update WorkCube set CreationDate = MDT
WHERE MDT IS NOT NULL;
Go

IF EXISTS (SELECT 1 FROM sys.columns  WHERE Name = 'MDT' AND Object_ID = Object_ID('dbo.WorkCube'))

BEGIN

ALTER TABLE dbo.WorkCube DROP COLUMN MDT;
END
GO
ALTER TABLE dbo.WorkCube ADD CONSTRAINT FK_WorkCube_EmployeeCode FOREIGN KEY(EmployeeCode) REFERENCES dbo.Employees(EmployeeCode) ON UPDATE NO ACTION ON DELETE NO ACTION;
go
 

----------------------------------------- End 2026-01-11 --------------------- 
----------------------------------------- Shehab 2026-01-12 --------------------
GO
INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], 
[ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES (1237, N'Military Data', 
N'بيانات التجنيد', 1218, N'/Personal/MilitaryData', N'Index', N'fas fa-user-shield', N'get', 21, 1, 1)

Go
INSERT [dbo].[SystemMenuModule] ([SystemMenuCode], [AppModuleCode]) VALUES (1237, 22)
GO

Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4395,N'بيانات التجنيد',N'Military Data',1237,1)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4396,N'عرض بيانات التجنيد',N'View Military Data',1237,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4397,N'إضافة بيانات التجنيد',N'Add Military Data',1237,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4398,N'تعديل بيانات التجنيد',N'Edit Military Data',1237,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4399,N' حذف بيانات التجنيد ',N'Delete Military Data',1237,2)
Go																																  
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4400,N'حفظ بيانات التجنيد',N'Save Military Data',1237,2)
GO

----------------------------------------- End 2026-01-12 --------------------- 
----------------------------------------- AK 2026-01-13 --------------------
INSERT INTO [SystemMenu](SystemMenuCode,Name,Arabic_Name,ParentID,ControllerName,ActionName,ImageURL,HTTPMethod,Sequence,IsVisible,ShowInPrivilegePage) VALUES (1238,N'Criminal Investigation Card',N'كرنية البحث الجنائي',1218,N'/Personal/EmployeeCriminalInvestigation',N'Index',N'fa fa-thumb-tack',N'get',1,1,1)
GO
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1238,22)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4401,N'كرنية البحث الجنائي',N'Criminal Investigation Card',1238,1)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4402,N'عرض كرنية البحث الجنائي',N'View Criminal Investigation Card',1238,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4403,N'إضافة كرنية البحث الجنائي',N'Add Criminal Investigation Card',1238,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4404,N'تعديل كرنية البحث الجنائي',N'Edit Criminal Investigation Card',1238,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4405,N'حذف كرنية البحث الجنائي',N'Delete Criminal Investigation Card',1238,2)
GO
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4406,N'حفظ كرنية البحث الجنائي',N'Save Criminal Investigation Card',1238,2)
GO
----------------------------------------- AK 2026-01-13 --------------------- 
----------------------------------------- End 2026-01-11 --------------------- 

----------------------------------------- Ali 2026-01-13 --------------------- 
ALTER TABLE dbo.VacationType
	DROP CONSTRAINT DF_VacationType_DayRateNumber
GO

UPDATE VacationType
SET DayRateNumber = NULL
WHERE DayRateNumber = 0;
Go
----------------------------------------- End 2026-01-13 --------------------- 
----------------------------------------- Mostafa 2026-01-14 --------------------- 

INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1239,N'Payroll Basis',N'أسس الاحتساب',1215,N'/Payroll/Basis',N'Index','fa-solid fa-calculator',N'get',2,1,1)
Go
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1239,22)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4407,N'أسس الاحتساب',N'Payroll Basis',1239,1)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4408,N'عرض أسس الاحتساب',N'View Payroll Basis',1239,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4409,N'إضافة أسس الاحتساب',N'Add Payroll Basis',1239,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4410,N'تعديل أسس الاحتساب',N'Edit Payroll Basis',1239,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4411,N'حذف أسس الاحتساب',N'Delete Payroll Basis',1239,2)
Go
INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4412,N'حفظ أسس الاحتساب',N'Delete Payroll Basis',1239,2)


----------------------------------------- End 2026-01-14 --------------------- 
----------------------------------------- Ali 2026-01-14 --------------------- 
UPDATE MilitaryData
SET CityCode = NULL
WHERE CityCode = 0;
Go
  delete from [MilitaryData] where  [EmployeeCode] not in(select [EmployeeCode] from Employees)
  Go


  
    UPDATE [MilitaryData]
SET [MilitaryCode] = NULL
WHERE [MilitaryCode] = 0;
Go

----------------------------------------- End 2026-01-14 ---------------------
----------------------------------------- Ali 2026-01-19 ---------------------

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = 'AllocationByLocation' AND object_id = OBJECT_ID('dbo.Basis'))
                  ALTER TABLE dbo.Basis ADD AllocationByLocation bit NULL 
Go
----------------------------------------- End 2026-01-19 ---------------------
----------------------------------------- shehab 2026-02-24 ---------------------
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1180,1)
Go
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1245,1) 
Go
----------------------------------------- End 2026-02-24 ---------------------




